﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.DataAccessProvider;
using DTN.Core;
using DTN.Core.Common;
using DTN.Core.Utilities;
namespace com.dtn.deals
{
    public class tbl_ProductManager : Itbl_ProductManager
    {
        private static Itbl_ProductManager itbl_ProductManager = null;
        private tbl_ProductManager()
        {
        }
        public static Itbl_ProductManager CreateInstance()
        {
            if (itbl_ProductManager == null)
            {
                itbl_ProductManager = new tbl_ProductManager();
            }
            return itbl_ProductManager;
        }
        public int Inserttbl_Product(tbl_Product tbl_product)
        {
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            int inumOfRowEffect = 0;
            SqlParameter[] parameters = new SqlParameter[]{
																new SqlParameter("@ID", SqlDbType.Int),
																new SqlParameter("@pId", SqlDbType.Int),
																new SqlParameter("@cityId", SqlDbType.Int),
																new SqlParameter("@proName", SqlDbType.NVarChar),
																new SqlParameter("@contact", SqlDbType.NVarChar),
																new SqlParameter("@saleOff", SqlDbType.NVarChar),
																new SqlParameter("@price", SqlDbType.Float),
																new SqlParameter("@votes", SqlDbType.Int),
																new SqlParameter("@voteSet", SqlDbType.Int),
																new SqlParameter("@promotion", SqlDbType.NVarChar),
																new SqlParameter("@rank", SqlDbType.TinyInt),
																new SqlParameter("@poisition", SqlDbType.NVarChar),
																new SqlParameter("@Info", SqlDbType.NVarChar),
																new SqlParameter("@timeFrame", SqlDbType.NVarChar),
																new SqlParameter("@cDate", SqlDbType.DateTime),
																new SqlParameter("@startDate", SqlDbType.DateTime),
																new SqlParameter("@endDate", SqlDbType.DateTime),
																new SqlParameter("@isDel", SqlDbType.TinyInt),
																new SqlParameter("@cMember", SqlDbType.NVarChar),
                                                                new SqlParameter("@cateID", SqlDbType.VarChar),
                                                                new SqlParameter("@parrent", SqlDbType.Int),
                                                                new SqlParameter("@AutoId", SqlDbType.VarChar),
                                                                new SqlParameter("@Stime", SqlDbType.Int),
                                                                new SqlParameter("@Etime", SqlDbType.Int)
                                                                
															};
            parameters[0].Direction = ParameterDirection.Output;
            parameters[1].Value = tbl_product.pId;
            parameters[2].Value = tbl_product.cityId;
            parameters[3].Value = tbl_product.proName;
            parameters[4].Value = tbl_product.contact;
            parameters[5].Value = tbl_product.saleOff;
            parameters[6].Value = tbl_product.price;
            parameters[7].Value = tbl_product.votes;
            parameters[8].Value = tbl_product.voteSet;
            parameters[9].Value = tbl_product.promotion;
            parameters[10].Value = tbl_product.rank;
            parameters[11].Value = tbl_product.poisition;
            parameters[12].Value = tbl_product.Info;
            parameters[13].Value = tbl_product.timeFrame;
            parameters[14].Value = tbl_product.cDate;
            parameters[15].Value = tbl_product.startDate;
            parameters[16].Value = tbl_product.endDate;
            parameters[17].Value = tbl_product.isDel;
            parameters[18].Value = tbl_product.cMember;
            parameters[19].Value = tbl_product.CateID;
            parameters[20].Value = tbl_product.Parrent;
            parameters[21].Value = tbl_product.AutoId;
            parameters[22].Value = tbl_product.STime;
            parameters[23].Value = tbl_product.ETime;
            try
            {
                inumOfRowEffect = dataProvider.ExecuteNonQuery("sp_Inserttbl_Product", CommandType.StoredProcedure, parameters);
                tbl_product.ID = Convert.ToInt32(parameters[0].Value);
            }
            catch (Exception ex)
            {
                tbl_product.ID = -1;
            }
            return tbl_product.ID;
        }
        //thêm khung giờ và saleoff tương ứng với sản phẩm khi insert
        public int Inserttbl_Tframe(tbl_Product tbl_product)
        {
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            int inumOfRowEffect = 0;
            SqlParameter[] parameters = new SqlParameter[]{
																new SqlParameter("@ID", SqlDbType.Int),
																new SqlParameter("@pId", SqlDbType.Int),
																new SqlParameter("@saleOff", SqlDbType.Int),
																new SqlParameter("@timeFrame", SqlDbType.NVarChar),
                                                                new SqlParameter("@Votes", SqlDbType.Int),
                                                                new SqlParameter("@cDate", SqlDbType.DateTime),
															};
            parameters[0].Direction = ParameterDirection.Output;
            parameters[1].Value = tbl_product.pId;
            parameters[2].Value = tbl_product.saleOff;
            parameters[3].Value = tbl_product.promotion;
            parameters[4].Value = tbl_product.votes;
            parameters[5].Value = tbl_product.cDate;
            try
            {
                inumOfRowEffect = dataProvider.ExecuteNonQuery("sp_Inserttbl_TFrame", CommandType.StoredProcedure, parameters);
                tbl_product.ID = Convert.ToInt32(parameters[0].Value);
            }
            catch (Exception ex)
            {
                tbl_product.ID = -1;
            }
            return tbl_product.ID;
        }

        public bool Updatetbl_Product(tbl_Product tbl_product)
        {
            bool bresult = false;
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            int inumOfRowEffect = 0;
            SqlParameter[] parameters = new SqlParameter[]{
																new SqlParameter("@ID", SqlDbType.Int),
																new SqlParameter("@pId", SqlDbType.Int),
																new SqlParameter("@cityId", SqlDbType.Int),
																new SqlParameter("@proName", SqlDbType.NVarChar),
																new SqlParameter("@contact", SqlDbType.NVarChar),
																new SqlParameter("@saleOff", SqlDbType.NVarChar),
																new SqlParameter("@price", SqlDbType.Float),
																new SqlParameter("@votes", SqlDbType.Int),
																//new SqlParameter("@voteSet", SqlDbType.Int),
																new SqlParameter("@promotion", SqlDbType.NVarChar),
																new SqlParameter("@rank", SqlDbType.TinyInt),
																new SqlParameter("@poisition", SqlDbType.NVarChar),
																new SqlParameter("@Info", SqlDbType.NVarChar),
																new SqlParameter("@timeFrame", SqlDbType.NVarChar),
																new SqlParameter("@cDate", SqlDbType.DateTime),
																new SqlParameter("@startDate", SqlDbType.DateTime),
																new SqlParameter("@endDate", SqlDbType.DateTime),
																new SqlParameter("@isDel", SqlDbType.TinyInt),
																new SqlParameter("@cMember", SqlDbType.NVarChar),
                                                                 new SqlParameter("@cateID", SqlDbType.VarChar),
															};
            parameters[0].Value = tbl_product.ID;
            parameters[1].Value = tbl_product.pId;
            parameters[2].Value = tbl_product.cityId;
            parameters[3].Value = tbl_product.proName;
            parameters[4].Value = tbl_product.contact;
            parameters[5].Value = tbl_product.saleOff;
            parameters[6].Value = tbl_product.price;
            parameters[7].Value = tbl_product.votes;
            //parameters[8].Value = tbl_product.voteSet;
            parameters[8].Value = tbl_product.promotion;
            parameters[9].Value = tbl_product.rank;
            parameters[10].Value = tbl_product.poisition;
            parameters[11].Value = tbl_product.Info;
            parameters[12].Value = tbl_product.timeFrame;
            parameters[13].Value = tbl_product.cDate;
            parameters[14].Value = tbl_product.startDate;
            parameters[15].Value = tbl_product.endDate;
            parameters[16].Value = tbl_product.isDel;
            parameters[17].Value = tbl_product.cMember;
            parameters[18].Value = tbl_product.CateID;
            try
            {
                inumOfRowEffect = dataProvider.ExecuteNonQuery("sp_Updatetbl_Product", CommandType.StoredProcedure, parameters);
                bresult = true;
            }
            catch (Exception ex)
            {
                bresult = false;
            }
            return bresult;
        }

        public bool Updatetbl_VoteSetProduct(int VoteSet, int ID)
        
        {
            bool bresult = false;
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            int inumOfRowEffect = 0;
            SqlParameter[] parameters = new SqlParameter[]{
																new SqlParameter("@ID", SqlDbType.Int),
																new SqlParameter("@voteSet", SqlDbType.Int),
															};
            parameters[0].Value = ID;
            parameters[1].Value = VoteSet;
            try
            {
                inumOfRowEffect = dataProvider.ExecuteNonQuery("sp_Updatetbl_ProductVoteSet", CommandType.StoredProcedure, parameters);
                bresult = true;
            }
            catch (Exception ex)
            {
                bresult = false;
            }
            return bresult;
        }

        public bool Updatetbl_Tframe(tbl_Product tbl_product)
        {
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            int inumOfRowEffect = 0;
            bool bresult = false;
            SqlParameter[] parameters = new SqlParameter[]{
																new SqlParameter("@ID", SqlDbType.Int),
																new SqlParameter("@Votes", SqlDbType.Int),
                                                                new SqlParameter("@saleOff", SqlDbType.NVarChar),
																new SqlParameter("@timeFrame", SqlDbType.NVarChar),
                                                                new SqlParameter("@ProName", SqlDbType.NVarChar),
															};
            parameters[0].Value = tbl_product.ID;
            parameters[1].Value = tbl_product.votes;
            parameters[2].Value = tbl_product.saleOff;
            parameters[3].Value = tbl_product.timeFrame;
            parameters[4].Value = tbl_product.proName;
            try
            {
                inumOfRowEffect = dataProvider.ExecuteNonQuery("sp_Updatetbl_TFrame", CommandType.StoredProcedure, parameters);
                bresult = true;
            }
            catch (Exception ex)
            {
                bresult = false;
            }
            return bresult;
        }
        public bool Deletetbl_Product(int id)
        {
            bool bresult = false;
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            int inumOfRowEffect = 0;
            SqlParameter[] parameters = new SqlParameter[]{
																new SqlParameter("@ID", SqlDbType.Int),
															};
            parameters[0].Value = id;
            try
            {
                inumOfRowEffect = dataProvider.ExecuteNonQuery("sp_Deletetbl_Product", CommandType.StoredProcedure, parameters);
                bresult = true;
            }
            catch (Exception ex)
            {
                bresult = false;
            }
            return bresult;
        }

        public bool Deletetbl_Tframe(int pid)
        {
            bool bresult = false;
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            int inumOfRowEffect = 0;
            SqlParameter[] parameters = new SqlParameter[]{
																new SqlParameter("@PID", SqlDbType.Int),
															};
            parameters[0].Value = pid;
            try
            {
                inumOfRowEffect = dataProvider.ExecuteNonQuery("sp_Deletetbl_Tframe", CommandType.StoredProcedure, parameters);
                bresult = true;
            }
            catch (Exception ex)
            {
                bresult = false;
            }
            return bresult;
        }
        public tbl_Product Selecttbl_ProductByID(int id)
        {
            IDataReader dr;
            tbl_Product tbl_product = new tbl_Product();
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            SqlParameter[] parameters = new SqlParameter[]{
																new SqlParameter("@ID", SqlDbType.Int),
															};
            parameters[0].Value = id;
            try
            {
                dr = dataProvider.ExecuteReader("sp_Selecttbl_Product", CommandType.StoredProcedure, parameters);
                while (dr.Read())
                {
                    tbl_product.ID = Convert.ToInt32(dr["ID"].ToString());
                    tbl_product.CateID = dr["cateId"].ToString();
                    tbl_product.pId = Convert.ToInt32(dr["pId"].ToString());
                    tbl_product.cityId = Convert.ToInt32(dr["cityId"].ToString());
                    tbl_product.proName = (dr["proName"].ToString());
                    tbl_product.contact = (dr["contact"].ToString());
                    tbl_product.saleOff = dr["saleOff"].ToString();
                    tbl_product.price = Convert.ToDouble(dr["price"].ToString());
                    tbl_product.votes = Convert.ToInt32(dr["votes"].ToString());
                    tbl_product.voteSet = Convert.ToInt32(dr["voteSet"].ToString());
                    tbl_product.promotion = (dr["promotion"].ToString());
                    tbl_product.rank = Convert.ToInt32(dr["rank"].ToString());
                    tbl_product.poisition = (dr["poisition"].ToString());
                    tbl_product.Info = (dr["Info"].ToString());
                    tbl_product.timeFrame = (dr["timeFrame"].ToString());
                    tbl_product.cDate = Convert.ToDateTime(dr["cDate"].ToString());
                    if (dr["startDate"].ToString() != string.Empty)
                    {
                        tbl_product.startDate = Convert.ToDateTime(dr["startDate"].ToString());
                    }
                    if (dr["endDate"].ToString() != string.Empty)
                    {
                        tbl_product.endDate = Convert.ToDateTime(dr["endDate"].ToString());
                    }
                    tbl_product.isDel = Convert.ToInt32(dr["isDel"].ToString());
                    tbl_product.cMember = (dr["cMember"].ToString());
                }

            }
            catch (Exception ex)
            {
                return null;
            }
            return tbl_product;
        }
        public DataTable Selecttbl_ProductsDynamic(string Where, string Order)
        {
            DataSet ds = new DataSet();
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            SqlParameter[] parameters = new SqlParameter[]{
																new SqlParameter("@WhereCondition", SqlDbType.NVarChar,500),
																new SqlParameter("@OrderByExpression", SqlDbType.NVarChar,250),
															};
            parameters[0].Value = Where;
            parameters[1].Value = Order;
            try
            {
                ds = dataProvider.ExecuteDataSet("sp_Selecttbl_ProductsDynamic", CommandType.StoredProcedure, parameters);
            }
            catch (Exception ex)
            {
                return null;
            }
            return ds.Tables["Table"];
        }
        public DataTable Selecttbl_ProductsWithPageDynamic(int StartIndex, int MaxRecords, string Where)
        {
            DataSet ds = new DataSet();
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            SqlParameter[] parrams = { new SqlParameter("@Startindex",SqlDbType.Int ){Value = StartIndex },
                                         new SqlParameter("@Maxrecords",SqlDbType.Int ){Value = MaxRecords  },
                                         new SqlParameter("@WhereCondition",SqlDbType.NVarChar,500 ){Value = Where  }
                                     };
            try
            {
                ds = dataProvider.ExecuteDataSet("sp_Selecttbl_ProductWithPagesDynamic", CommandType.StoredProcedure, parrams);
            }
            catch (Exception ex)
            {
                return null;
            }
            return ds.Tables["Table"];
        }
        //Cuong Code
        public static DataTable GetByPartnerIdAndBetween(int partnerid, int top, string date)
        {
            DataTable retVal = null;
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("tbl_Product_GetByPartnerIdAndBetween", dbConn);
            dbCmd.Parameters.AddWithValue("@Top", top);
            dbCmd.Parameters.AddWithValue("@PartnerId", partnerid);
            dbCmd.Parameters.AddWithValue("@Date", date);
            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                retVal = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }


        public DataTable Statistic_SelectAllDealAndPaging(int StartIndex, int MaxRecords, string Where, string WhereOrder)
        {
            DataSet ds = new DataSet();
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            SqlParameter[] parrams = {   new SqlParameter("@Startindex",SqlDbType.Int ){Value = StartIndex },
                                         new SqlParameter("@Maxrecords",SqlDbType.Int ){Value = MaxRecords  },
                                         new SqlParameter("@WhereCondition",SqlDbType.NVarChar,500 ){Value = Where  },
                                         new SqlParameter("@WhereConditionOrder",SqlDbType.NVarChar,500 ){Value = WhereOrder  }
                                     };
            try
            {
                ds = dataProvider.ExecuteDataSet("sp_statistic_SelectAllDealWithPaging", CommandType.StoredProcedure, parrams);
            }
            catch (Exception ex)
            {
                return null;
            }
            return ds.Tables["Table"];
        }

        

        public static int GetTopSlale(int partnerId)
        {
            int retVal = 0;
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);

            SqlCommand dbCmd = new SqlCommand("tbl_Product_GetTopSlale", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@PartnerId", partnerId);
            try
            {
                dbConn.Open();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                object returnRole = dbCmd.ExecuteScalar();
                if (returnRole != null && returnRole != DBNull.Value)
                    retVal = ConvertUtility.ToInt32(returnRole);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public static tbl_Product GetInfo(int iD)
        {
            tbl_Product retVal = null;
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("tbl_Product_GetInfo", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.Add("@ID", iD);
            try
            {
                dbConn.Open();
                SqlDataReader dr = dbCmd.ExecuteReader();
                if (dr.Read()) retVal = new tbl_Product(dr);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
    }
}
